--SQL Hacks, Hack 80: Play Six Degrees of Kevin Bacon
--oracle version

--------------
--   setup  --
--------------

CREATE TABLE performsIn
(actor   VARCHAR(50)
,movie   VARCHAR(50)
,PRIMARY KEY (actor,movie)
);
INSERT INTO performsIn VALUES ('Kevin Bacon','Friday the 13th');
INSERT INTO performsIn VALUES ('Kevin Bacon','Murder in the First');
INSERT INTO performsIn VALUES ('Kevin Bacon','Footloose');
INSERT INTO performsIn VALUES ('Kevin Bacon','Diner');
INSERT INTO performsIn VALUES ('Adrienne King','Friday the 13th');
INSERT INTO performsIn VALUES ('Kevin Bacon','Flatliners');
INSERT INTO performsIn VALUES ('Aeryk Egan','Flatliners');
INSERT INTO performsIn VALUES ('Kevin Bacon','She''s having a Baby');
INSERT INTO performsIn VALUES ('Alex Baldwin','She''s having a Baby');
INSERT INTO performsIn(movie,actor) VALUES ('Diner','Timothy Daly');
INSERT INTO performsIn(movie,actor) VALUES ('Object of My Affecti','Alan Alda');
INSERT INTO performsIn(actor,movie) VALUES ('Timothy Daly','Object of My Affecti');
INSERT INTO performsIn(movie,actor) VALUES ('Picture Perfect','Kevin Bacon');
INSERT INTO performsIn(movie,actor) VALUES ('Picture Perfect','Jennifer Aniston');
INSERT INTO performsIn(movie,actor) VALUES ('Object of My Affecti','Alan Alda');
INSERT INTO performsIn(actor,movie) VALUES ('Jennifer Aniston','Object of My Affecti');
INSERT INTO performsIn(movie,actor) VALUES ('My Dog Skip','Kevin Bacon');
INSERT INTO performsIn(movie,actor) VALUES ('My Dog Skip','Diane Lane');
INSERT INTO performsIn(movie,actor) VALUES ('Murder at 1600','Alan Alda');
INSERT INTO performsIn(actor,movie) VALUES ('Diane Lane','Murder at 1600');
INSERT INTO performsIn(movie,actor) VALUES ('Few Good Men, A','Kevin Bacon');
INSERT INTO performsIn(movie,actor) VALUES ('Few Good Men, A','Kevin Pollak');
INSERT INTO performsIn(movie,actor) VALUES ('Canadian Bacon','Alan Alda');
INSERT INTO performsIn(actor,movie) VALUES ('Kevin Pollak','Canadian Bacon');
INSERT INTO performsIn(movie,actor) VALUES ('Hollow Man','Josh Brolin');
INSERT INTO performsIn(movie,actor) VALUES ('Hollow Man','Kevin Bacon');
INSERT INTO performsIn(movie,actor) VALUES ('Flirting with Disast','Alan Alda');
INSERT INTO performsIn(actor,movie) VALUES ('Josh Brolin','Flirting with Disast');
INSERT INTO performsIn(movie,actor) VALUES ('Planes, Trains &amp; Aut','Kevin Bacon');
INSERT INTO performsIn(movie,actor) VALUES ('Planes, Trains &amp; Aut','John Candy');
INSERT INTO performsIn(movie,actor) VALUES ('Canadian Bacon','Alan Alda');
INSERT INTO performsIn(actor,movie) VALUES ('John Candy','Canadian Bacon');

INSERT INTO performsIn(movie,actor) VALUES ('Apollo 13','Kevin Bacon');
INSERT INTO performsIn(movie,actor) VALUES ('Tremors','Kevin Bacon');
INSERT INTO performsIn(movie,actor) VALUES ('Apollo 13','Ed Harris');
INSERT INTO performsIn(movie,actor) VALUES ('Tremors','Fred Ward');
INSERT INTO performsIn(movie,actor) VALUES ('Tremors','Fred Ward');
INSERT INTO performsIn(actor,movie) VALUES ('Ed Harris','Creepshow');
INSERT INTO performsIn(actor,movie) VALUES ('Fred Ward','Naked Gun 3');
INSERT INTO performsIn(actor,movie) VALUES ('Fred Ward','Naked Gun 3');
INSERT INTO performsIn(movie,actor) VALUES ('Creepshow','Leslie Nie');
INSERT INTO performsIn(movie,actor) VALUES ('Naked Gun 3','Leslie Nie');
INSERT INTO performsIn(actor,movie) VALUES ('George Ken','Naked Gun:');
INSERT INTO performsIn(movie,actor) VALUES ('Naked Gun:','Weird Al');|
--------------------
--       hack     --
--------------------

SELECT * FROM performsIn WHERE actor = 'Kevin Bacon';
SELECT x.actor,x.movie,y.actor
  FROM performsIn x JOIN performsIn y ON (x.movie=y.movie)
 WHERE x.actor='Kevin Bacon'
 ORDER BY y.actor;
CREATE VIEW actorToActor AS
  SELECT x.actor actorIn ,x.movie, y.actor actorOut
    FROM performsIn x JOIN performsIn y 
        ON (x.movie=y.movie AND x.actor!=y.actor);

SELECT movie, actorOut
  FROM actorToActor
 WHERE actorIn='Kevin Bacon'
ORDER BY actorOut;


COL actorIn FORMAT a20
COL movie   FORMAT a20
COL actorOut FORMAT a20;

SELECT x.actor actorIn ,x.movie, y.actor actorOut
    FROM performsIn x JOIN performsIn y 
        ON (x.movie=y.movie AND x.actor!=y.actor)
  WHERE LEVEL<3
  START WITH x.actor = 'Kevin Bacon'
  CONNECT BY NOCYCLE PRIOR x.actor = y.actor


SELECT actorIn, movie, actorOut, LEVEL
 FROM actorToActor
WHERE LEVEL<3
  START WITH actorIn = 'Kevin Bacon'
  CONNECT BY NOCYCLE PRIOR actorIn = actorOut


SELECT DISTINCT actorOut, MIN(LEVEL)
 FROM actorToActor
WHERE LEVEL<4
  START WITH actorIn = 'Kevin Bacon'
  CONNECT BY NOCYCLE PRIOR actorIn = actorOut
GROUP BY actorOut

SELECT actorIn, movie, actorOut
 FROM actorToActor
  START WITH actorIn = 'Kevin Bacon'
  CONNECT BY PRIOR actorIn = actorOut
;


COL actor1  FORMAT a15
COL movie1  FORMAT a15
COL actor2  FORMAT a15
COL movie2  FORMAT a15
SELECT  x.movie    movie1
       ,x.actorOut actor1
       ,y.movie    movie2
       ,y.actorOut actor2
  FROM actorToActor x JOIN actorToActor y ON (x.actorOut=y.actorIn)
 WHERE x.actorIn  = 'Kevin Bacon'
   AND y.actorOut = 'Alan Alda';


SELECT  LEFT(x.movie,20)    movie1
       ,LEFT(x.actorOut,20) actor1
       ,LEFT(y.movie,20)    movie2
       ,LEFT(y.actorOut,10) actor2
  FROM actorToActor x JOIN actorToActor y ON (x.actorOut=y.actorIn)
 WHERE x.actorIn  = 'Kevin Bacon'
   AND y.actorOut = 'Alan Alda';

SELECT  LEFT(x.movie,10)    movie1
       ,LEFT(x.actorOut,10) actor1
       ,LEFT(y.movie,11)    movie2
       ,LEFT(y.actorOut,10) actor2
       ,LEFT(z.movie,11)    movie3
       ,LEFT(z.actorOut,10) actor3
  FROM actorToActor x JOIN actorToActor y ON x.actorOut=y.actorIn
                      JOIN actorToActor z ON y.actorOut=z.actorIn
 WHERE x.actorIn  = 'Kevin Bacon'
   AND z.actorOut = '''Weird Al'' Yankovic';

SELECT COUNT(*) FROM(
SELECT  LEFT(w.movie,10)    movie1
       ,LEFT(w.actorOut,10) actor1
       ,LEFT(z.movie,11)    movie3
       ,LEFT(z.actorOut,10) actor3
  FROM actorToActor w JOIN actorToActor x ON w.actorOut=x.actorIn
                      JOIN actorToActor y ON x.actorOut=y.actorIn
                      JOIN actorToActor z ON y.actorOut=z.actorIn
 WHERE w.actorIn  = 'Kevin Bacon'
   AND z.actorOut = 'Kevin Bacon'
) t;
----------------
--    tidy    --
----------------

DROP VIEW actorToActor;
DROP TABLE performsIn;
